一、回顧： 第六堂 相關情境練習

(一) 訂單表頭資料(Orders)、員工資料(Employees)

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_datediff

(二) 題目:員工最高業績月份

從Orders 訂單表頭所有的資料當中，找出每一個員工在哪一個月訂單數目最高，那個月有幾筆訂單，並依照最高的訂單數量由高到低列出

列出欄位：

• 員工完整姓名(Employees.Last Name + Employees.FirstName)
• 最高的訂單月份
• 高的訂單數量

(三) 參考答案：

SELECT CONCAT(Employees.LastName, ' ',Employees.FirstName) AS Full_Name,
       Employee_Order_Date.OrderDate AS Max_Order_Date,
       Employee_Max_Order_Count.Max_Order_Count
FROM (SELECT Employee_Order_Count.EmployeeID,
             MAX(Employee_Order_Count.OrderNum) AS Max_Order_Count
      FROM (SELECT EmployeeID,
                   COUNT(OrderID) AS OrderNum,
                   CONCAT(YEAR(OrderDate), '-', MONTH(OrderDate)) AS OrderDate
            FROM Orders
            GROUP BY CONCAT(YEAR(OrderDate), '-', MONTH(OrderDate)),
                     EmployeeID
            ) AS Employee_Order_Count
      GROUP BY Employee_Order_Count.EmployeeID
     ) AS Employee_Max_Order_Count
INNER JOIN (SELECT EmployeeID,
                   COUNT(OrderID) AS OrderNum,
                   CONCAT(YEAR(OrderDate), '-', MONTH(OrderDate)) AS OrderDate
            FROM Orders
            GROUP BY CONCAT(YEAR(OrderDate), '-', MONTH(OrderDate)),
                     EmployeeID
           ) AS Employee_Order_Date
ON Employee_Max_Order_Count.EmployeeID = Employee_Order_Date.EmployeeID AND
   Employee_Max_Order_Count.Max_Order_Count = Employee_Order_Date.OrderNum
INNER JOIN Employees
ON Employee_Max_Order_Count.EmployeeID = Employees.EmployeeID
ORDER BY Max_Order_Count DESC

(四) 說明

1. 統計每個員工在每個月的訂單數量

SELECT EmployeeID,
       COUNT(OrderID) AS OrderNum,
       CONCAT(YEAR(OrderDate), '-', MONTH(OrderDate)) AS OrderDate
FROM Orders
GROUP BY CONCAT(YEAR(OrderDate), '-', MONTH(OrderDate)),
         EmployeeID

查詢結果(只列部分資料)：

2. 依照員工、月份分類，統計訂單最高的月份的訂單數量

  SELECT Employee_Order_Count.EmployeeID,
         MAX(Employee_Order_Count.OrderNum) AS Max_Order_Count
  FROM (SELECT EmployeeID,
               COUNT(OrderID) AS OrderNum,
               CONCAT(YEAR(OrderDate), '-', MONTH(OrderDate)) AS OrderDate
        FROM Orders
        GROUP BY CONCAT(YEAR(OrderDate), '-', MONTH(OrderDate)),
                 EmployeeID
        ) AS Employee_Order_Count 
  GROUP BY Employee_Order_Count.EmployeeID 
    

查詢結果：

3. 和所有員工每一個月的訂單總和資料合併

如果使用聚合函數，沒有分類的欄位無法放在SELECT
所以使用另一個子查詢，重複第1步，得到每個員工在每個月的訂單數量
然後跟第二步查詢到「員工訂單最高的月份的訂單數量」的資料
使用「員工」和「數量」合併在一起

SELECT Employee_Order_Count.EmployeeID,
       Employee_Order_Date.OrderDate AS Max_Order_Date,
       Employee_Max_Order_Count.Max_Order_Count
FROM (SELECT Employee_Order_Count.EmployeeID,
			 MAX(Employee_Order_Count.OrderNum) AS Max_Order_Count
	  FROM (SELECT EmployeeID,
                   COUNT(OrderID) AS OrderNum,
                   CONCAT(YEAR(OrderDate), '-', MONTH(OrderDate)) AS OrderDate
            FROM Orders
            GROUP BY CONCAT(YEAR(OrderDate), '-', MONTH(OrderDate)),
                     EmployeeID
            ) AS Employee_Order_Count
       GROUP BY Employee_Order_Count.EmployeeID
     ) AS Employee_Max_Order_Count
INNER JOIN (SELECT EmployeeID,
                   COUNT(OrderID) AS OrderNum,
                   CONCAT(YEAR(OrderDate), '-', MONTH(OrderDate)) AS OrderDate
            FROM Orders
            GROUP BY CONCAT(YEAR(OrderDate), '-', MONTH(OrderDate)),
                     EmployeeID
           ) AS Employee_Order_Date
ON Employee_Max_Order_Count.EmployeeID = Employee_Order_Date.EmployeeID AND
   Employee_Max_Order_Count.Max_Order_Count = Employee_Order_Date.OrderNum

4. 合併員工資料表，將員工ID替換成員工全名

SELECT CONCAT(Employees.LastName, ' ',Employees.FirstName) AS Full_Name,
       Employee_Order_Date.OrderDate AS Max_Order_Date,
       Employee_Max_Order_Count.Max_Order_Count

(... 中間省略...)

INNER JOIN Employees
ON Employee_Max_Order_Count.EmployeeID = Employees.EmployeeID

5. 依照統計的訂單數量大小，從最高的員工排序到數量最低的員工

ORDER BY Max_Order_Count DESC

二、第六堂範例：家事曠工清單＆次數統計表

(一) 需求：

身為父親的泰D，想要知道近期哪些小孩子有乖乖工作，哪些小孩子好吃懶做。

(二) 實作結果

SELECT  CAST(CleanSchedule.CleanDateTime AS DATE) AS 偷懶日期,
        FamilyName 偷懶成員,
        ItemName 沒做的工作
FROM CleanSchedule
INNER JOIN Family
ON CleanSchedule.FamilyId = Family.FamilyId
INNER JOIN CleanItemList
ON CleanSchedule.CleanItem = CleanItemList.CleanItem
WHERE CleanScheduleId NOT IN( SELECT  CleanScheduleId
                              FROM    CleanRecord
                              WHERE CleanSchedule.CleanScheduleId = CleanRecord.CleanScheduleId)
ORDER BY CleanSchedule.CleanDateTime,
         Family.FamilyId;

(三) 自行練習變化：偷懶次數統計表

(四) 資料來源

1. 家庭成員資料表

SELECT *
FROM Family;

2. 家事項目清單表

SELECT *
FROM CleanItemList;

3. 家事排班表

SELECT *
FROM CleanSchedule;

4. 實際做家事紀錄表

SELECT *
FROM CleanRecord;

三、準備資料

模擬資料庫平台：DB Fiddle

https://www.db-fiddle.com/

資料範本 DDL SQL

/* 家庭成員資料表 */
-- 傾印  表格 ithome2019_familydb.family 結構
CREATE TABLE `Family` (
  `FamilyId` varchar(100) NOT NULL,
  `FamilyName` varchar(10) DEFAULT NULL,
  `FamilySex` varchar(10) DEFAULT NULL,
  `BirthDate` datetime DEFAULT NULL,
  `PhoneNumber` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`FamilyId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `Family` (`FamilyId`, `FamilyName`, `FamilySex`, `BirthDate`, `PhoneNumber`) VALUES
	('34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '泰肝', '女生', '2007-10-11 00:00:00', ''),
	('3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '泰熱', '男生', '2001-12-10 00:00:00', '0934567890'),
	('91b18f1f-4ef8-4066-97c4-28daea585db5', '泰胖', '女生', '2003-05-13 00:00:00', ''),
	('91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '泰瘦', '女生', '1985-07-10 00:00:00', '0944623456'),
	('bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '泰冷', '男生', '1995-01-23 00:00:00', '0977654258'),
	('de8cb5db-2061-4d35-a662-ba5f528fadba', '泰賢', '男生', '1977-02-03 00:00:00', '0944589456');
    
/* 家事項目清單 */
CREATE TABLE `CleanItemList` (
  `CleanItem` varchar(50) NOT NULL,
  `ItemName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`CleanItem`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `CleanItemList` (`CleanItem`, `ItemName`) VALUES
	('0', '打掃'),
	('1', '拖地'),
	('2', '洗碗'),
	('3', '倒垃圾');

/* 家事排班一覽表 */
CREATE TABLE `CleanSchedule` (
  `CleanScheduleId` varchar(100) NOT NULL,
  `CleanDateTime` datetime DEFAULT NULL,
  `FamilyId` varchar(100) DEFAULT NULL,
  `CleanItem` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`CleanScheduleId`),
  KEY `FK_cleanschedule_family` (`FamilyId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `CleanSchedule` (`CleanScheduleId`, `CleanDateTime`, `FamilyId`, `CleanItem`) VALUES
	('0208B1E1-4F72-4545-8722-E220894526BB', '2019-08-17 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2'),
	('046188FB-B13C-45B9-9EA0-0A998B24263A', '2019-08-13 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '5'),
	('0C7CF82D-D75A-452A-9775-39A47A0E1F08', '2019-08-15 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '5'),
	('10DCAA5C-5E44-416D-A758-1846DFA72837', '2019-08-20 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '3'),
	('150A422A-3A65-4404-9C9A-61A820C13D17', '2019-08-23 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '1'),
	('1D0CCF2A-B5F9-4A3D-B701-328B26CFB522', '2019-08-22 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '5'),
	('2049F029-0CAA-4249-99C4-5F647B09526D', '2019-08-20 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('2B7E17DA-E144-4832-B6B9-A54C1EE4FA49', '2019-08-15 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('2D682CA3-B900-41B0-9AD9-5611296DFBE5', '2019-08-18 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2'),
	('34931A84-85E7-4236-B1C4-01D190EEFE27', '2019-08-16 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '0'),
	('34D5124E-21FE-4A74-BD25-1190B063D0EE', '2019-08-14 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '3'),
	('37F40275-D8DD-43A2-B37E-2DB649A8BA3B', '2019-08-22 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '3'),
	('384343F7-AC7E-4C71-BFC9-9D5AFCE58113', '2019-08-19 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '4'),
	('3A37A6D9-8ED8-4DF2-BB67-B71560DA1D34', '2019-08-14 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2'),
	('3CC296C1-172B-4EBB-81C0-67B1A849B978', '2019-08-14 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('3FDF9E95-82EB-4A91-811F-870DA2C0F788', '2019-08-13 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('43483F07-889A-4BA8-B0D5-149FBCD270F8', '2019-08-14 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '5'),
	('4508A7DA-A570-4918-86DF-591686233A9C', '2019-08-15 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '3'),
	('4B4E212B-C40B-4233-B3B3-3531D6FE7915', '2019-08-18 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('56AA47E8-E0E0-4224-8EA9-96950B6B7AC5', '2019-08-15 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('5B29E024-BB11-4336-ADD7-60443851F599', '2019-08-23 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '0'),
	('5CD82BE7-E711-4BB1-B1C5-7D729CAAB02D', '2019-08-20 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '0'),
	('5CFD5FD4-1999-4EBA-B31B-5A2BD1B8C0E5', '2019-08-19 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '3'),
	('5EF225E5-DE30-46A0-AFF9-2A9DA7D5AF1E', '2019-08-15 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '4'),
	('6698F721-F6E6-4C58-93E9-1EC39FC3A50D', '2019-08-13 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '4'),
	('685D2679-4C17-4FB9-9FB8-EC6F856DA121', '2019-08-17 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '4'),
	('695A1809-37DE-416E-970E-DF43857ADC14', '2019-08-20 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '5'),
	('70AD02FA-9FD7-4AB6-B22C-D3D33F48CD54', '2019-08-13 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '3'),
	('71CBD806-D0E4-4E87-8ACF-1BF9995EF69C', '2019-08-16 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '1'),
	('74A6DFBE-FFE8-4013-9C7B-E17640D36475', '2019-08-21 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '4'),
	('7BE1E533-FDE3-40EA-9A4F-E7638BA1E168', '2019-08-18 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '5'),
	('7E54D4BD-3DD2-4696-8DBC-BCCB0FD71EE2', '2019-08-17 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '1'),
	('83595DE1-12FE-453D-9DDD-6ED7D0A355F4', '2019-08-18 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '4'),
	('83B129E4-E37F-4F1F-B013-C2B2CECC8181', '2019-08-20 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2'),
	('843D27D9-CB11-446B-AF65-563C641D872B', '2019-08-16 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '3'),
	('8C1F882A-A36B-4023-B311-25D7EC1F8EA8', '2019-08-17 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '3'),
	('905AEEEF-2750-4934-9CE5-87416027F09B', '2019-08-22 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '1'),
	('98ADC0FA-508E-4117-8075-4567995A8C45', '2019-08-19 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '1'),
	('9B1B4243-2C42-4268-A4FB-A5FE95DF57E6', '2019-08-21 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '1'),
	('9B3946B3-C184-4EFE-B005-568D930101FD', '2019-08-14 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '4'),
	('9EFD2E6E-15E9-48CE-B8C9-75033D75AD00', '2019-08-14 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('A1596044-F368-46CB-9D2F-8C286C51BEF9', '2019-08-22 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '4'),
	('A25F668E-5E12-4E15-A71D-9ACBC51200A5', '2019-08-13 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '1'),
	('A47EA3A8-3EAC-4171-92B2-B91B5478C957', '2019-08-21 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2'),
	('ABA175B5-2865-4396-8B0A-39E889A8FAE0', '2019-08-21 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '0'),
	('AE3D7002-EB24-4105-ABB3-FB7ABB9E175A', '2019-08-19 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '0'),
	('BE41D9A4-358B-41E6-A016-9829701E2DCF', '2019-08-17 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '5'),
	('C0F392D2-0461-4589-88A9-7E4C3E24C79E', '2019-08-19 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2'),
	('C23CAA9C-8C63-4F74-8251-1A0773B18104', '2019-08-23 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2'),
	('C464A299-5EF7-4999-B4F2-578B20BBB524', '2019-08-23 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '5'),
	('C6727911-38B7-40F8-9C4F-730F652A67D0', '2019-08-22 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2'),
	('C78F99F6-37B6-4FD5-AD04-D586BF032D1C', '2019-08-16 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '5'),
	('CDEC8205-C50F-4688-BF10-3600A2689753', '2019-08-21 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '5'),
	('CE8F3CFB-5959-451B-94B9-95CFBE77DAFB', '2019-08-19 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '5'),
	('CEC240B4-7988-479F-BEEE-7D39999067B3', '2019-08-17 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '0'),
	('D420FFDD-6BAE-435F-85AE-57D917EA63ED', '2019-08-18 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '3'),
	('D60541EA-5A4E-46F9-8322-BDE6A0E5380D', '2019-08-21 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '3'),
	('E2EC6280-307F-4E47-BA9E-0A08EF4602DD', '2019-08-23 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '3'),
	('EF29679A-4952-4F37-AE3F-8F2A14A5A0FF', '2019-08-13 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2'),
	('F7B09E72-69BA-4D00-A157-5E36DD1ED4CE', '2019-08-15 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2'),
	('F802E6A4-5F10-4985-BFD2-0D5C4435EF6A', '2019-08-18 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('F80F67C5-6849-4FF7-B2C5-739268BD51BF', '2019-08-23 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '4'),
	('FA20D45B-9B64-4C61-9148-270FF13CF38E', '2019-08-20 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '4'),
	('FA6FC631-97B1-4A9E-807D-2B520ACB7D28', '2019-08-16 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2'),
	('FA796E8C-5B52-46DF-8910-F23080B9ECA7', '2019-08-22 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '0'),
	('FF257219-DB3E-4CBB-8E63-C5A85B09950A', '2019-08-16 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '4;');


/* 實際做家事紀錄表 */
CREATE TABLE `CleanRecord` (
  `CleanRecordId` varchar(100) NOT NULL,
  `CleanScheduleId` varchar(100) DEFAULT NULL,
  `FamilyId` varchar(100) DEFAULT NULL,
  `StartTime` datetime DEFAULT NULL,
  `EndTime` datetime DEFAULT NULL,
  PRIMARY KEY (`CleanRecordId`),
  KEY `FK_cleanrecord_cleanschedule` (`CleanScheduleId`),
  KEY `FK_cleanrecord_family` (`FamilyId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `CleanRecord` (`CleanRecordId`, `CleanScheduleId`, `FamilyId`, `StartTime`, `EndTime`) VALUES
	('1BBFDF7D-D064-4091-925B-0541C27BD321', '5CD82BE7-E711-4BB1-B1C5-7D729CAAB02D', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-20 18:12:00', '2019-08-20 19:02:00'),
	('38F23254-67F5-4AB5-A8DE-929501AB802E', '150A422A-3A65-4404-9C9A-61A820C13D17', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-23 18:52:00', '2019-08-23 20:19:00'),
	('457B8655-44A1-4F59-8B45-7DB0FD2E1A8E', 'AE3D7002-EB24-4105-ABB3-FB7ABB9E175A', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-19 18:30:00', '2019-08-19 19:32:00'),
	('4911A9BD-BE90-4174-990B-A72498678A92', '8C1F882A-A36B-4023-B311-25D7EC1F8EA8', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-17 18:09:00', '2019-08-17 19:56:00'),
	('49292BD5-27D1-40E8-8A64-73F6A99211C9', '2049F029-0CAA-4249-99C4-5F647B09526D', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-20 18:21:00', '2019-08-20 19:12:00'),
	('4A182CCE-4267-4451-A456-3A0B4CD3ED36', '2B7E17DA-E144-4832-B6B9-A54C1EE4FA49', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-15 18:04:00', '2019-08-15 19:34:00'),
	('4E10D641-A5E9-4FCF-A2EF-5B1896A091D2', '56AA47E8-E0E0-4224-8EA9-96950B6B7AC5', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-15 18:43:00', '2019-08-15 19:23:00'),
	('5E4206B9-1078-41FF-BF54-A2920A37D843', 'C6727911-38B7-40F8-9C4F-730F652A67D0', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-22 18:59:00', '2019-08-22 20:21:00'),
	('65B732DE-AE07-45E6-8D14-C356E1BDEBE9', '3CC296C1-172B-4EBB-81C0-67B1A849B978', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-14 18:10:00', '2019-08-14 19:40:00'),
	('675FFDEF-1000-4507-87A6-8DAEDD2247BD', 'D60541EA-5A4E-46F9-8322-BDE6A0E5380D', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-21 18:01:00', '2019-08-21 19:43:00'),
	('6E2CB5C0-FDD3-4ABA-B38B-BC4915A458EE', '37F40275-D8DD-43A2-B37E-2DB649A8BA3B', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-22 18:14:00', '2019-08-22 20:19:00'),
	('7353332C-2E99-48CB-900F-BDE2B7FE39B6', '83B129E4-E37F-4F1F-B013-C2B2CECC8181', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-20 18:29:00', '2019-08-20 19:57:00'),
	('79D7DF92-8A68-4AF1-B5FE-7733EEB495E6', '905AEEEF-2750-4934-9CE5-87416027F09B', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-22 18:44:00', '2019-08-22 19:21:00'),
	('7D4085F9-614D-41FB-AE16-1442E13DEACD', '3A37A6D9-8ED8-4DF2-BB67-B71560DA1D34', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-14 18:31:00', '2019-08-14 19:25:00'),
	('8A284F82-BB85-4B10-A56A-4B07B79A2D80', 'D420FFDD-6BAE-435F-85AE-57D917EA63ED', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-18 18:37:00', '2019-08-18 20:21:00'),
	('8FBCF266-C104-4113-B3DB-461ACAD3048B', '843D27D9-CB11-446B-AF65-563C641D872B', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '2019-08-16 18:38:00', '2019-08-16 19:40:00'),
	('A7960AAC-6F6D-4B58-94BD-2253253C572D', 'E2EC6280-307F-4E47-BA9E-0A08EF4602DD', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-23 18:10:00', '2019-08-23 20:16:00'),
	('A8D86DAE-4135-4090-9D57-04F2D585DD61', '98ADC0FA-508E-4117-8075-4567995A8C45', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2019-08-19 18:09:00', '2019-08-19 20:11:00'),
	('ADF35E1E-1760-4012-98CF-CE0B5751C096', 'A25F668E-5E12-4E15-A71D-9ACBC51200A5', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-13 18:09:00', '2019-08-13 19:16:00'),
	('B56F7CC2-EC30-4EB0-9969-5058FDE390AC', 'F7B09E72-69BA-4D00-A157-5E36DD1ED4CE', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-15 18:15:00', '2019-08-15 19:02:00'),
	('BA54CEF9-55EC-449B-BA32-E48AA84EAFD1', '10DCAA5C-5E44-416D-A758-1846DFA72837', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-20 18:03:00', '2019-08-20 19:51:00'),
	('DAF64DBF-0B83-40CF-A616-D7DD8A538C7C', '9B1B4243-2C42-4268-A4FB-A5FE95DF57E6', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-21 18:41:00', '2019-08-21 20:19:00'),
	('DB564B2A-F025-43D9-8D27-8E14E717FB4C', '2D682CA3-B900-41B0-9AD9-5611296DFBE5', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2019-08-18 18:47:00', '2019-08-18 20:07:00'),
	('DE5FA643-1485-4CC5-B913-82CC5A989929', '9EFD2E6E-15E9-48CE-B8C9-75033D75AD00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-14 18:48:00', '2019-08-14 20:12:00'),
	('E5BE6337-DDEB-4107-A5A6-FAD1F3DD4422', 'FA6FC631-97B1-4A9E-807D-2B520ACB7D28', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2019-08-16 18:52:00', '2019-08-16 19:36:00'),
	('E8A0FFAB-BADD-4DCD-8278-F32BD0C06922', '7E54D4BD-3DD2-4696-8DBC-BCCB0FD71EE2', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '2019-08-17 18:45:00', '2019-08-17 19:59:00'),
	('EA538BCF-FE8E-45A8-9BEF-7C08ABBFCB60', '71CBD806-D0E4-4E87-8ACF-1BF9995EF69C', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '2019-08-16 18:27:00', '2019-08-16 19:49:00');

四、認識 WHERE IN 與 WHERE EXISTS

泰D必須從「排班表」的排班當中，每一筆資料的流水號，在「實際做家事紀錄表」沒有出現的做家事的排班流水號

白話文：有排班要做家事，但沒有做的家事

1. 使用 純 JOIN + 判斷 IS NULL

SELECT  CAST(CleanSchedule.CleanDateTime AS DATE) AS 偷懶日期,
        FamilyName 偷懶成員,
        ItemName 當日負責工作
FROM CleanSchedule
LEFT JOIN CleanRecord
ON CleanSchedule.CleanScheduleId = CleanRecord.CleanScheduleId
INNER JOIN Family
ON CleanSchedule.FamilyId = Family.FamilyId
INNER JOIN CleanItemList
ON CleanSchedule.CleanItem = CleanItemList.CleanItem
WHERE CleanRecord.CleanRecordId IS NULL
ORDER BY CleanSchedule.CleanDateTime,
         Family.FamilyId

2. Where In：篩選A查詢當中所有資料的某個欄位，有出現在B查詢所有資料的某個欄位

使用時機：子查詢資料小的時候

寫法

SELECT *
FROM  A查詢的資料表或資料來源
WHERE A查詢的某格欄位 IN (SELECT B查詢的某個欄位
                        FROM A查詢的資料表或資料來源)

實際SQL

前面加上NOT，代表「沒有出現在」子查詢某個欄位的資料

SELECT  CAST(CleanSchedule.CleanDateTime AS DATE) AS 偷懶日期,
        FamilyName 偷懶成員,
        ItemName 當日負責工作
FROM CleanSchedule
INNER JOIN Family
ON CleanSchedule.FamilyId = Family.FamilyId
INNER JOIN CleanItemList
ON CleanSchedule.CleanItem = CleanItemList.CleanItem
WHERE CleanScheduleId NOT IN( SELECT  CleanScheduleId
                              FROM    CleanRecord
                              WHERE CleanSchedule.CleanScheduleId = CleanRecord.CleanScheduleId)
ORDER BY CleanSchedule.CleanDateTime,
         Family.FamilyId

3. Where EXISTS： 找到符合子查詢的資料。如果子查詢的資料不存在，不會執行外面的查詢

使用時機：子查詢資料很大的時候，或者判斷有沒有兩個以上的欄位資較出現在子查詢

寫法

SELECT *
FROM  A查詢的資料表或資料來源
WHERE EXISTS  (SELECT *
               FROM B查詢
               WHERE A查詢.A查詢資料的某個欄位 = B查詢.B查詢資料的某個欄位)

SELECT  CAST(CleanSchedule.CleanDateTime AS DATE) AS 偷懶日期,
		FamilyName 偷懶成員,
		ItemName 當日負責工作
FROM CleanSchedule
INNER JOIN Family
ON CleanSchedule.FamilyId = Family.FamilyId
INNER JOIN CleanItemList
ON CleanSchedule.CleanItem = CleanItemList.CleanItem
WHERE NOT EXISTS( SELECT  CleanScheduleId
                  FROM    CleanRecord
                  WHERE CleanSchedule.CleanScheduleId = CleanRecord.CleanScheduleId)
ORDER BY CleanSchedule.CleanDateTime,
         Family.FamilyId

觀念介紹：
https://www.1keydata.com/tw/sql/sql-exists.html

五、英文延伸閱讀： SQL Server IN vs EXISTS 的使用時機

https://www.mssqltips.com/sqlservertip/6013/sql-server-in-vs-exists/

六、中文延伸閱讀： SQL Server-聚焦IN VS EXISTS VS JOIN效能分析

https://www.cnblogs.com/CreateMyself/p/6165982.html

七、相關情境練習

使用資料表：Orders、Customers

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_datediff

題目：下半年客戶別訂單數量統計

分別使用 純JOIN、IN、EXISTS 三種寫法，統計1996-07-01 ~ 1996-12-31 的所有訂單當中，依照客戶別，統曾經下訂過的客戶分別訂了幾筆訂單

列出欄位：

• 客戶姓名(Customers資料表的CustomerName)
• 客戶下半年下訂的訂單數量